package web.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel 读取器
 */
public class ExcelReader {
    private static void readXlsx(List<Object> result, InputStream is, IExcelRowMapper mapper) throws Exception {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow == null) {
                    continue;
                }
                result.add(mapper.mapFromXlsx(xssfRow));
            }
        }
    }

    private static void readXls(List<Object> result, InputStream is, IExcelRowMapper mapper) throws Exception {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }

            // 循环行Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }
                result.add(mapper.mapFromXls(hssfRow));
            }
        }
    }

    public static List<Object> readExcel(InputStream inputStream, IExcelRowMapper mapper, boolean isXlsx) throws Exception {
        List<Object> result = new ArrayList<Object>();
        if (isXlsx)
            readXlsx(result, inputStream, mapper);
        else
            readXls(result, inputStream, mapper);
        return result;
    }

    public static Object getValueFromXssf(XSSFCell xssfCell) {
        switch (xssfCell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING: // 字符串
                return xssfCell.getStringCellValue();
            case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                return xssfCell.getBooleanCellValue();
            case XSSFCell.CELL_TYPE_FORMULA: // 公式  按照算出来的结果取值
                return xssfCell.getNumericCellValue();
//            case HSSFCell.CELL_TYPE_BLANK: // 空值
//                return null;
//            case HSSFCell.CELL_TYPE_ERROR: // 故障
//                return null;
            case XSSFCell.CELL_TYPE_NUMERIC: // 数值型
                Object value = null;
                if (HSSFDateUtil.isCellDateFormatted(xssfCell)) {
                    //  如果是date类型则 ，获取该cell的date值
                    value = xssfCell.getDateCellValue();
                } else { // 纯数字 解决excel中很多位数据的问题
                    BigDecimal bd = new BigDecimal(xssfCell.getNumericCellValue());
                    value = bd.toString();
//                    value = xssfCell.getNumericCellValue();
                }
                return value;
        }
        return null;
    }

    public static Object getValueFromHssf(HSSFCell hssfCell) {
        switch (hssfCell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING: // 字符串
                return hssfCell.getStringCellValue();
            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                return hssfCell.getBooleanCellValue();
            case HSSFCell.CELL_TYPE_FORMULA: // 公式  按照算出来的结果取值
                return hssfCell.getNumericCellValue();
//            case HSSFCell.CELL_TYPE_BLANK: // 空值
//                return null;
//            case HSSFCell.CELL_TYPE_ERROR: // 故障
//                return null;
            case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
                Object value = null;
                if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
                    //  如果是date类型则 ，获取该cell的date值
                    value = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()).toString();
                } else { // 纯数字
//                    value = hssfCell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(hssfCell.getNumericCellValue());
                    value = bd.toString();
                }
                return value;
        }
        return null;
    }
}
